﻿CREATE PROCEDURE [dbo].[GetAuction]

AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT a.LelongId
      ,a.LelongTipCode
      ,a.Address
      ,a.Price
      ,a.Type
      ,a.LandArea
      ,a.AuctionDate
      ,a.Description
      ,c.UserName as AgentName
      ,c.SmallURL as StaffPicURL
      ,c.Mobile as AgentMobile
      ,c.Email as Email
      ,a.CreateDate
      ,a.UpdateDate --,b.CompanyName,b.MobilePhone
      ,max(d.SmallUrl) as PropertyURL
  FROM AW_Lelong a --,Mobile_Settings b
  left join AW_Staff c on c.StaffId = a.AgentID
  left join AW_Picture d on d.PropertyId = a.LelongId
  where a.StatusDelete=null or a.StatusDelete=0
  group by a.LelongId,a.LelongTipCode,a.Address
      ,a.Price
      ,a.Type
      ,a.LandArea,a.AuctionDate
      ,a.Description,c.UserName
      ,c.SmallURL 
      ,c.Mobile
      ,c.Email
      ,a.CreateDate
      ,a.UpdateDate
  order by a.UpdateDate desc

END



